03 March 2025

Database Changes for 4.39

This section contains details of database changes between the 4.38 and 4.39 releases.

Table changes:

The following tables have been added.

Table Name Description
EDGE_ROLES Roles used to control user interaction with the third party links in Engage.
SFG_CATEGORIES Categories that will be set for safeguarding note, form and meeting templates.
SFG_DISTRIBUTION_LIST_LINKS Links staff and groups to safeguarding distribution lists.
SFG_DISTRIBUTION_LISTS Named distribution lists for safeguarding records, notes and meetings.
SFG_RECORD_TEMPLATE_CTGY Links safeguarding categories to safeguarding record templates.
SFG_RECORD_TEMPLATE_DIST Links safeguarding distribution lists and individual staff members to safeguarding record templates.
SFG_RECORD_TEMPLATES Named safeguarding record templates.
SFG_TEMPLATE_DEFINITIONS Named safeguarding template form definitions.

The following columns have been added.

Table Name Column Name Type (Size) Nullable Description
ATTAINMENTS QUALRESULT nvarchar(30) Y The qualification result held by the Student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=QUALRESULT)].
LEARNING_PROG IDPCODE nvarchar(1) Y Records whether a learner has an Individual Development Plan(LP90) [FK=ELWA_Verifiers.CODE (RV_DOMAIN=IDPINDICATOR)].
LOCATIONS INCLUDE_IN_SPACEFTE nvarchar(1) N Indicates Locations where enrolments need to have the CDR Spacial FTE calculation performed [Y/N] [DEFAULT=N].
PEOPLE_CDR GCSE_A_C numeric(2, 0) Y Number of GCSEs grade A to C or equivalent.
PEOPLE_CDR GCSE_D_G numeric(2, 0) Y Number of GCSEs grade D to G or equivalent.
PEOPLE_CDR CARE_LEAVER nvarchar(2) Y Whether student is a Care Leaver [FK=CDR_Verifiers.CODE (RV_DOMAIN=CARE_LEAVER)].
PEOPLE_HESA SID numeric(17, 0) Y The Student Identifier.
PEOPLE_HESA SCN nvarchar(9) Y The Scottish Candidate Number.
PEOPLE_HESA CARER nvarchar(2) Y Indicates whether a student is a carer [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=CARER)].
PEOPLE_HESA TRANS nvarchar(2) Y The transgender status of the student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=TRANS)].
PEOPLE_HESA LANGPREF nvarchar(2) Y The language in which the student would prefer to be contacted [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LANGPREF)].
PEOPLE_HESA SERLEAVE nvarchar(2) Y Indicates whether the student is a service leaver, according to the relevant body definition [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SERLEAVE)].
PEOPLE_HESA SERSTU nvarchar(2) Y Indicates whether the student is a service student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SERSTU)].
PEOPLE_HESA TTACCOM nvarchar(2) Y The type of accommodation the student is living in during term time [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=TTACCOM)].
PEOPLE_HESA LANGUAGEID nvarchar(2) Y The language ID in which the student proficiency is recorded [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=LANGUAGEID)].
PEOPLE_HESA PROFICIENCYTYPE nvarchar(2) Y The type of proficiency the student has in the language indicated [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PROFICIENCYTYPE)].
PEOPLE_HESA PROFICIENCYLEVEL nvarchar(2) Y The students ability to speak in the language indicated [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=PROFICIENCYLEVEL)].
PEOPLE_HESA ACCESSPRG nvarchar(2) Y Indicates whether entrants have entered HE via SWAP courses [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ACCESSPRG)].
PEOPLE_HESA DEPENDANT nvarchar(2) Y Indicates whether the student has any dependants [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=DEPENDANT)].
PEOPLE_HESA ESTRANGED nvarchar(2) Y Indicates whether the student is estranged, based on their own self-assessment [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=ESTRANGED)].
PEOPLE_HESA MARSTAT nvarchar(2) Y The marital status of the Student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=MARSTAT)].
PEOPLE_HESA RELIGIOUSBGROUND nvarchar(2) Y The religious background that the student was brought up in [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=RELIGIOUSBGROUND)].
PEOPLE_HESA YRLLPROV nvarchar(4) Y The year in which the student left the previous provider.
PEOPLE_HESA SOC2010 nvarchar(4) Y The standard occupational classification code for the student [FK=HESA_VERIFIERS.CODE(RV_DOMAIN=SOC2010)].
PEOPLE_UNITS_CDR HLA_PATHWAY nvarchar(5) Y Code to identify the correct HLA pathway for the learner at enrolment level as defined by DfE [FK=Verifiers.LOW_VALUE (RV_DOMAIN=U_HLA_PATHWAY)].
RRQ_ORGANISATIONS OFQUAL_STATUS nvarchar(21) Y The Ofqual status of the organisation.
RRQ_ORGANISATIONS OFQUAL_RECOG_FROM datetime Y The date an organisation became recognised by Ofqual.
RRQ_ORGANISATIONS OFQUAL_RECOG_TO datetime Y The date an organisation stopped being recognised by Ofqual.
RRQ_ORGANISATIONS CCEA_REG_STATUS nvarchar(21) Y The CCEA regulation status of the organisation.
RRQ_ORGANISATIONS CCEA_REG_RECOG_FROM datetime Y The date an organisation became recognised by CCEA regulation.
RRQ_ORGANISATIONS CCEA_REG_RECOG_TO datetime Y The date an organisation stopped being recognised by CCEA regulation.
THIRDPARTY_LINKS EDGE_ROLE nvarchar(250) Y A comma delimited list of edge roles to control the usage of a third party link, constructed by associating a third party link to one or more edge roles.
UIO_CDR HECOS_CODE nvarchar(10) Y Current HECOS code for HE courses [FK=CDR_Verifiers.CODE (RV_DOMAIN=HECOS_CODE)].
UIO_CDR HLA_PATHWAY nvarchar(5) Y Code to identify the correct HLA pathway for the learner at course level as defined by DfE [FK=Verifiers.LOW_VALUE (RV_DOMAIN=U_HLA_PATHWAY)].

The following columns have been changed.

Table Name Column Name Type (Size) Nullable Previous Values Type (Size) [Nullable]
ATTAINMENTS HESA_QUAL_TYPE nvarchar(5) Y nvarchar(2) [Y]
ATTAINMENTS MAIN_SUBJECT_1 nvarchar(6) Y nvarchar(4) [Y]
LEARNING_PROG EMP_NAME nvarchar(200) Y nvarchar(60) [Y]
ORGANISATION_UNITS FES_FULL_NAME nvarchar(200) Y nvarchar(100) [Y]
PEOPLE COLLEGE_EMAIL nvarchar(320) Y nvarchar(128) [Y]
PEOPLE PERSONAL_EMAIL nvarchar(320) Y nvarchar(100) [Y]

The following columns have been added.

View Name Column Name
EBS_LEARNER_ENROLMENTS HLA_PATHWAY
EBS_LEARNERS GCSE_A_C
EBS_LEARNERS GCSE_D_G
EBS_LEARNERS P_CDR_CARE_LEAVER
EBS_LEARNERS_DEDUP GCSE_A_C
EBS_LEARNERS_DEDUP GCSE_D_G
EBS_LEARNERS_DEDUP P_CDR_CARE_LEAVER
EBS_LLWR_LEARNING_PROG IDPCODE
EBS_PEOPLE_LP_GROUPS DATE_OF_BIRTH
EBS_PEOPLE_LP_GROUPS IDPCODE
EBS_UIO HECOS_CODE
EBS_UIO HLA_PATHWAY
EBS_UIO_LLWR HECOS_CODE
EBS_UIO_LLWR HLA_PATHWAY

The following views have been dropped.

  • DM_APP_APPLICATIONS_NI

  • DM_CPL_EFA

  • DM_CUR_COURSE_KIS

  • DM_CUR_COURSE_SUMMARY_NI

  • DM_ENQ_LEARNER_ENQUIRIES_NI

  • dm_sys_wamusers

  • ebs_de_failure_attend_school

  • ebs_de_suspension

  • EBS_EXAM_BOARDS_NO_PLR

  • ebs_learner_employment_TEMP

  • EBS_LEARNER_ENROLMENTS2

  • Perfomance

  • v_rep_studentPastoralStatus

  • v_report_ilpTargets

  • WEST_LEARNERS

The following columns have been dropped.

Table Name Description
EBS_STAFFUSER relationship
EBS_STAFFUSER_ANSWS relationship
EBS_STAFFUSER_NSW relationship